import os
import sqlite3
import polars as pl


class SQLite:
    def __init__(self, dbpath):
        if not os.path.isabs(dbpath):
            raise ValueError("数据库文件路径必须是绝对路径。")
        self.dbpath = dbpath
        with sqlite3.connect(self.dbpath) as conn:
            self._create_tables(conn)

    @staticmethod
    def get_table_schemas():
        return {
            "verify_status": """
                   CREATE TABLE IF NOT EXISTS verify_status (
                       bill_no            TEXT    NOT NULL,
                       box_list_no        TEXT    NOT NULL,
                       verify_time        DATETIME,
                       is_verified        INTEGER NOT NULL DEFAULT 0 CHECK(is_verified IN (0,1)),
                       PRIMARY KEY (bill_no, box_list_no)
                   )
               """
        }

    def create_table_if_not_exists(self, conn, table_name, schema):
        cursor = conn.cursor()
        cursor.execute(schema)

    def _create_tables(self, conn):
        for table_name, schema in self.get_table_schemas().items():
            self.create_table_if_not_exists(conn, table_name, schema)
        self._create_triggers(conn)

    def _create_triggers(self, conn):
        cursor = conn.cursor()
        trigger_sql = """
CREATE TRIGGER IF NOT EXISTS trg_set_verify_time
AFTER UPDATE OF is_verified ON verify_status
WHEN OLD.is_verified = 0 AND NEW.is_verified = 1
BEGIN
    UPDATE verify_status
       SET verify_time = datetime('now', '+8 hours')
     WHERE bill_no      = NEW.bill_no
       AND box_list_no  = NEW.box_list_no;
END;
        """
        cursor.execute(trigger_sql)
        conn.commit()

    def connect(self):
        return sqlite3.connect(self.dbpath)

    def execute(self, sql, parameters=None):
        with self.connect() as conn:
            cursor = conn.cursor()
            if parameters:
                cursor.execute(sql, parameters)
            else:
                cursor.execute(sql)
            conn.commit()
            return cursor.rowcount

    def executemany(self, sql, parameters_list):
        with self.connect() as conn:
            cursor = conn.cursor()
            cursor.executemany(sql, parameters_list)
            conn.commit()
            return cursor.rowcount

    # 查询,这个方法将接受一个SQL查询和一个值作为参数,value参数是可选的，将用于参数化查询
    # 并返回一个Polars DataFrame。
    # 如果没有提供值,则返回所有行。
    def query(self, sql, value=None):
        with self.connect() as conn:
            if value is None:
                return pl.read_database(
                    query=sql,
                    connection=conn,
                    #schema_overrides={"normalised_score": pl.UInt8},
                )
            else:
                return pl.read_database(
                    query=sql,
                    connection=conn,
                    execute_options={"parameters": {"value": value}},
                )

    def insert_verify_status(self, data):
        sql = "INSERT OR IGNORE INTO verify_status (bill_no, box_list_no) VALUES (?, ?)"
        self.executemany(sql, data)

    def update_verify_status(self, bill_no, box_list_no, is_verified):
        sql = "UPDATE verify_status SET is_verified = ? WHERE bill_no = ? AND box_list_no = ?"
        self.execute(sql, (is_verified, bill_no, box_list_no))

    def delete_verify_status(self, bill_no, box_list_no):
        sql = "DELETE FROM verify_status WHERE bill_no = ? AND box_list_no = ?"
        self.execute(sql, (bill_no, box_list_no))

    def delete_verify_status_by_bill_no(self, bill_no):
        sql = "DELETE FROM verify_status WHERE bill_no = ?"
        self.execute(sql, (bill_no,))


# if __name__ == "__main__":
#     sqlite = SQLite('D:\\invoice_verification.db')
    # 带参数查询
    # df = sqlite.query("SELECT id,Item,CertificateCode,battCode FROM CertificateCode WHERE CertificateCode=:value",
    #                   'PEKSZ202112040448')
    # # 不带参数查询
    # df = sqlite.query("SELECT id,Item,CertificateCode,battCode FROM CertificateCode")